﻿//sqliteLink 使用System.Data.SQLite简单封装sqlite

using NLog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace tuyaxia.MissJane
{
    public class SqliteLink
    {       
        bool isopen= false;//conn是否打开
        SQLiteConnection conn = null;//
        SQLiteDataAdapter apt = null;
        SQLiteDataReader reader = null;
        SQLiteCommand cmd = null;
        DataRow dr=null;
        DataSet ds = null;
        DataTable dt = null;
        Dictionary<string, SQLiteDataAdapter> aptDic=null;
        Logger log;//nlog

        /// <summary>
        /// 构建,实列SQLiteCommand.
        /// </summary>
        public SqliteLink(string dbfile)
        {
            log = LogManager.GetCurrentClassLogger();//nlog
            conn = new SQLiteConnection("Data Source =" + Environment.CurrentDirectory + "\\"+dbfile);
            cmd = new SQLiteCommand(conn);
            ds = new DataSet();
            aptDic = new Dictionary<string, SQLiteDataAdapter>();

        }

        /// <summary>
        /// 把一个表全部放到combobox里，
        /// </summary>
        public void readToComboBox(string table, System.Windows.Forms.ComboBox cb,int group) {


            if (group < 0) cmd.CommandText = "select * from " + table;
            else cmd.CommandText = "select * from " + table + " where setGroup=  \' " + group+ " \'";
            if (isopen == false) { conn.Open(); isopen =true; }
            if (isopen == true)
            {
                reader = cmd.ExecuteReader();
                if(reader.FieldCount==1)
                { 
                    while (reader.Read())
                    {
                    cb.Items.Add(reader.GetString(0));
                    }
                }
                if (reader.FieldCount >1)
                {
                    while (reader.Read())
                    {
                        cb.Items.Add(reader.GetString(0));
                        
                    }
                }
            }
                reader.Close();
                conn.Close();
                isopen = false;
            }

        
        /// <summary>
        ///  用部分表填充到dataset
        /// </summary>
        public void fillTable(string table,int count=0,bool addApt=false)
        {
            if (count < 0)  cmd.CommandText = "select * from " + table;            
            else cmd.CommandText = "select * from " + table + " limit " + count; 

          // 
           
             if (apt == null)//如果没有SQLiteDataAdapter
            {
                apt = new SQLiteDataAdapter(cmd.CommandText, conn);                             
            }
            else
            {
                apt.SelectCommand = cmd;              
            }

            apt.Fill(ds, table);
            if (addApt && !aptDic.ContainsKey(table))
            {
                apt = new SQLiteDataAdapter(cmd.CommandText, conn);
                SQLiteCommandBuilder sb = new SQLiteCommandBuilder(apt);

                aptDic.Add(table, apt);
                apt=null;

            }
        }

        /// <summary>
        ///  更新数据库
        /// </summary>
        public void addRecord(string table, string[] row)
        {
            if (!ds.Tables.Contains(table)) fillTable(table, 0, true);


            dt = ds.Tables[table];
            dr = dt.NewRow();
            if (dt.Columns.Count== row.Length)
            {
                for (int i = 0; i < row.Length ; i++)
                {
                    if (row[i].Equals("null")) continue;
                    dr[i] = row[i];
                }
                dt.Rows.Add(dr);
            }
            else { log.Info("输入数与表列不等{0}", table); }
        }
        /// <summary>
        ///  更新数据库
        /// </summary>
        public int upData(string table)
        {
            //if(ds!=null&&)
           
            return aptDic[table].Update(ds,table);
            

        }
        public int upData()
        {
            //if(ds!=null&&)

            return apt.Update(ds);

        }

        public void addTemp(string table, string sql)
        {
            
        }
        /// <summary>
        /// 使用ｄｂ中预定义ｓｑｌ查询
        /// </summary>
        public string salesCount(string var,string table="sqlcom")
        {

            if (!ds.Tables.Contains(table))  fillTable(table,-1);
           
          //DataRow[] drs= ds.Tables[table].Select("name like '%日销量%'")[0]["val"];
            cmd.CommandText = ds.Tables[table].Select("name = \'"+var+" \'")[0][1].ToString();
            cmd.Connection.Open();
            
            string str = "";
           reader= cmd.ExecuteReader();

            while (reader.Read())
            {
                if (reader.FieldCount > 1)
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (reader[i].Equals(DBNull.Value))
                        {
                            str += reader.GetName(i) + ":无 "; 
                        }
                        else
                        {
                            str += reader.GetName(i) + ":" + reader[i].ToString() + "  ";
                        }
                        
                    }
                }



             
                // str = "销售额:" + reader[0]+ "利润:" + reader[1];
                // log.Info("{0}", reader.GetInt32(0));
            }
           
            reader.Close();
            cmd.Connection.Close();
            return str;
        }
       
        ~SqliteLink()
        {

        }
    }
}